{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selecting Columns Programmatically Using Column Expressions Tutorial\n",
    "\n",
    "MLDB provides a complete implementation of the [SQL](../../../../doc/#builtin/sql/Sql.md.html) SELECT statement. Most of the functions you are accustomed to using [are available](../../../../doc/#builtin/sql/ValueExpression.md.html) in your queries. \n",
    "\n",
    "MLDB is different from traditional SQL databases in that there is no enforced schema on rows, allowing you to work with millions of columns of sparse data. This makes it easy to load and manipulate sparse datasets, even when there are millions of columns. To reduce the size of your dataset or use only specific variables, we may need to select columns based on specific critera. [Column Expressions](../../../../doc/#builtin/sql/SelectExpression.md.html) is an MLDB extension that provides additional control over your column selection. With a column expression, you can programmatically return specific columns with a SQL `SELECT` statement.\n",
    "\n",
    "In this tutorial, we will provide examples of <code>COLUMN EXPR</code> within <code>SELECT</code> statements. This tutorial assumes familiarity with [Procedures](../../../../doc/#builtin/procedures/Procedures.md.html) and [Datasets](../../../../doc/#builtin/datasets/Datasets.md.html). We suggest going through the [Procedures and Functions Tutorial](../../../../ipy/notebooks/_tutorials/_latest/Procedures%20and%20Functions%20Tutorial.ipynb) and the [Loading Data Tutorial](../../../../ipy/notebooks/_tutorials/_latest/Loading%20Data%20Tutorial.ipynb) beforehand."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setting up\n",
    "\n",
    "The notebook cells below use `pymldb`'s `Connection` class to make [REST API](../../../../doc/#builtin/WorkingWithRest.md.html) calls. You can check out the [Using `pymldb` Tutorial](../../../../doc/nblink.html#_tutorials/Using pymldb Tutorial) for more details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pymldb import Connection\n",
    "mldb = Connection()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic usage example"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's begin by loading and visualizing our data. We will be using the dataset from the [Virtual Manipulation of Datasets Tutorial](../../../../ipy/notebooks/_tutorials/_latest/Virtual%20Manipulation%20of%20Datasets%20Tutorial.ipynb). We had chosen the [`tokenize`](../../../../doc/#builtin/sql/ValueExpression.md.html#builtinfunctions) function to count the number of words in the Wikipedia descriptions of several Machine Learning concepts (please check out the [tutorial](../../../../ipy/notebooks/_tutorials/_latest/Virtual%20Manipulation%20of%20Datasets%20Tutorial.ipynb) for more details)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put(\"/v1/procedures/import_ML_concepts\", {\n",
    "    \"type\":\"import.text\",\n",
    "    \"params\": {\n",
    "        \"dataFileUrl\":\"http://public.mldb.ai/datasets/MachineLearningConcepts.csv\",\n",
    "        \"outputDataset\":{\n",
    "            \"id\":\"ml_concepts\",\n",
    "            \"type\": \"sparse.mutable\"\n",
    "        },\n",
    "        \"named\": \"Concepts\",\n",
    "        \"select\": \"\"\" \n",
    "            tokenize(\n",
    "                lower(Text), \n",
    "                {splitChars: ' -''\"?!;:/[]*,().',  \n",
    "                minTokenLength: 4}) AS *\n",
    "        \"\"\",\n",
    "        \"runOnCreation\": True\n",
    "    }\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each word is represented by a column and each Machine Learning concept by a row. We can run a simple `SELECT` query to take a quick look at the first 5 rows of our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>addition</th>\n",
       "      <th>algorithm</th>\n",
       "      <th>algorithms</th>\n",
       "      <th>also</th>\n",
       "      <th>analysis</th>\n",
       "      <th>analyze</th>\n",
       "      <th>applications</th>\n",
       "      <th>approach</th>\n",
       "      <th>assigns</th>\n",
       "      <th>associated</th>\n",
       "      <th>...</th>\n",
       "      <th>popularized</th>\n",
       "      <th>provide</th>\n",
       "      <th>rather</th>\n",
       "      <th>recurrent</th>\n",
       "      <th>serve</th>\n",
       "      <th>stored</th>\n",
       "      <th>systems</th>\n",
       "      <th>threshold</th>\n",
       "      <th>understanding</th>\n",
       "      <th>wrong</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Support vector machine</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Logistic regression</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Deep belief network</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Restricted boltzmann machines</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hopfield network</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 286 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               addition  algorithm  algorithms  also  \\\n",
       "_rowName                                                               \n",
       "Support vector machine                1          2           1     1   \n",
       "Logistic regression                 NaN        NaN         NaN   NaN   \n",
       "Deep belief network                 NaN        NaN         NaN     1   \n",
       "Restricted boltzmann machines       NaN          1           2   NaN   \n",
       "Hopfield network                    NaN        NaN         NaN     1   \n",
       "\n",
       "                               analysis  analyze  applications  approach  \\\n",
       "_rowName                                                                   \n",
       "Support vector machine                1        1             1         1   \n",
       "Logistic regression                 NaN      NaN           NaN       NaN   \n",
       "Deep belief network                 NaN      NaN           NaN       NaN   \n",
       "Restricted boltzmann machines       NaN      NaN             1       NaN   \n",
       "Hopfield network                    NaN      NaN           NaN       NaN   \n",
       "\n",
       "                               assigns  associated  ...    popularized  \\\n",
       "_rowName                                            ...                  \n",
       "Support vector machine               1           1  ...            NaN   \n",
       "Logistic regression                NaN         NaN  ...            NaN   \n",
       "Deep belief network                NaN         NaN  ...            NaN   \n",
       "Restricted boltzmann machines      NaN         NaN  ...            NaN   \n",
       "Hopfield network                   NaN         NaN  ...              1   \n",
       "\n",
       "                               provide  rather  recurrent  serve  stored  \\\n",
       "_rowName                                                                   \n",
       "Support vector machine             NaN     NaN        NaN    NaN     NaN   \n",
       "Logistic regression                NaN     NaN        NaN    NaN     NaN   \n",
       "Deep belief network                NaN     NaN        NaN    NaN     NaN   \n",
       "Restricted boltzmann machines      NaN     NaN        NaN    NaN     NaN   \n",
       "Hopfield network                     1       1          1      1       1   \n",
       "\n",
       "                               systems  threshold  understanding  wrong  \n",
       "_rowName                                                                 \n",
       "Support vector machine             NaN        NaN            NaN    NaN  \n",
       "Logistic regression                NaN        NaN            NaN    NaN  \n",
       "Deep belief network                NaN        NaN            NaN    NaN  \n",
       "Restricted boltzmann machines      NaN        NaN            NaN    NaN  \n",
       "Hopfield network                     1          1              1      1  \n",
       "\n",
       "[5 rows x 286 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"SELECT * FROM ml_concepts LIMIT 5\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 286 columns, some of which may or may not be useful to the data analysis at hand. For example, we may want to rebuild a dataset with:\n",
    "* verbs and adverbs that end with \"ing\"\n",
    "* words that appear at least twice in each of the descriptions of the Machine Learning concepts.\n",
    "\n",
    "This can be done in a few queries as you will see below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using column expressions to keep columns that end with \"ing\"\n",
    "\n",
    "[Column Expressions](../../../../doc/#builtin/sql/SelectExpression.md.html) provide efficient ways of picking and choosing our columns. For example, we can only choose verbs and adverbs that end with \"ing\" to understand the overall meaning of a description.\n",
    "\n",
    "We use the [`columnName`](../../../../doc/#builtin/sql/SelectExpression.md.html) column expression function along with the [`LIKE`](../../../../doc/#builtin/sql/ValueExpression.md.html) SQL expression, as you will see below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>belonging</th>\n",
       "      <th>clustering</th>\n",
       "      <th>learning</th>\n",
       "      <th>making</th>\n",
       "      <th>mapping</th>\n",
       "      <th>performing</th>\n",
       "      <th>preprocessing</th>\n",
       "      <th>training</th>\n",
       "      <th>using</th>\n",
       "      <th>estimating</th>\n",
       "      <th>being</th>\n",
       "      <th>starting</th>\n",
       "      <th>depending</th>\n",
       "      <th>filtering</th>\n",
       "      <th>modelling</th>\n",
       "      <th>understanding</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Support vector machine</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Logistic regression</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Deep belief network</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Restricted boltzmann machines</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hopfield network</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               belonging  clustering  learning  making  \\\n",
       "_rowName                                                                 \n",
       "Support vector machine                 1           3         5       1   \n",
       "Logistic regression                  NaN         NaN       NaN     NaN   \n",
       "Deep belief network                  NaN         NaN         2     NaN   \n",
       "Restricted boltzmann machines        NaN         NaN         2     NaN   \n",
       "Hopfield network                     NaN         NaN       NaN     NaN   \n",
       "\n",
       "                               mapping  performing  preprocessing  training  \\\n",
       "_rowName                                                                      \n",
       "Support vector machine               1           1              1         2   \n",
       "Logistic regression                NaN         NaN            NaN       NaN   \n",
       "Deep belief network                NaN         NaN            NaN         2   \n",
       "Restricted boltzmann machines      NaN         NaN            NaN         1   \n",
       "Hopfield network                   NaN         NaN            NaN       NaN   \n",
       "\n",
       "                               using  estimating  being  starting  depending  \\\n",
       "_rowName                                                                       \n",
       "Support vector machine             1         NaN    NaN       NaN        NaN   \n",
       "Logistic regression                3           1    NaN       NaN        NaN   \n",
       "Deep belief network              NaN         NaN      1         1        NaN   \n",
       "Restricted boltzmann machines    NaN         NaN    NaN       NaN          1   \n",
       "Hopfield network                 NaN         NaN    NaN       NaN        NaN   \n",
       "\n",
       "                               filtering  modelling  understanding  \n",
       "_rowName                                                            \n",
       "Support vector machine               NaN        NaN            NaN  \n",
       "Logistic regression                  NaN        NaN            NaN  \n",
       "Deep belief network                  NaN        NaN            NaN  \n",
       "Restricted boltzmann machines          1          1            NaN  \n",
       "Hopfield network                     NaN        NaN              1  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "    SELECT COLUMN EXPR (WHERE columnName() LIKE '%ing')\n",
    "    FROM ml_concepts\n",
    "    LIMIT 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is very powerful because the [`LIKE`](../../../../doc/#builtin/sql/ValueExpression.md.html) statement in Standard SQL is typically found in row operations and more rarely in column operations. MLDB makes it simple to use such SQL expressions on columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using column expressions to keep columns that appear in multiple descriptions\n",
    "\n",
    "With [Column Expressions](../../../../doc/#builtin/sql/SelectExpression.md.html), we can select columns based on specific row selection criteria. <code/>COLUMN EXPR</code> will allow us for example to choose words that appear in multiple descriptions. In this case, we filter on words that show up at least 4 times.\n",
    "\n",
    "To achieve the desired outcome, we use a [Built-in Function](../../../../doc/#builtin/sql/SelectExpression.md.html) available in column expressions called `rowCount`. `rowCount` iterates through each column and returns the number of rows that have a value for the specific column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>learning</th>\n",
       "      <th>machine</th>\n",
       "      <th>machines</th>\n",
       "      <th>model</th>\n",
       "      <th>training</th>\n",
       "      <th>used</th>\n",
       "      <th>with</th>\n",
       "      <th>network</th>\n",
       "      <th>neural</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Support vector machine</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Logistic regression</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Deep belief network</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Restricted boltzmann machines</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hopfield network</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Naive bayes classifier</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Boltzmann machine</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Autoencoder</th>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Artificial neural network</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               learning  machine  machines  model  training  \\\n",
       "_rowName                                                                      \n",
       "Support vector machine                5        1         2      2         2   \n",
       "Logistic regression                 NaN      NaN       NaN      4       NaN   \n",
       "Deep belief network                   2        1         1      1         2   \n",
       "Restricted boltzmann machines         2        1         3    NaN         1   \n",
       "Hopfield network                    NaN      NaN       NaN      1       NaN   \n",
       "Naive bayes classifier                2        1         1      1         1   \n",
       "Boltzmann machine                     3        2         2    NaN         1   \n",
       "Autoencoder                           2      NaN       NaN    NaN       NaN   \n",
       "Artificial neural network             1        1       NaN    NaN       NaN   \n",
       "\n",
       "                               used  with  network  neural  \n",
       "_rowName                                                    \n",
       "Support vector machine            2     1      NaN     NaN  \n",
       "Logistic regression               1     1      NaN     NaN  \n",
       "Deep belief network             NaN     1        4       1  \n",
       "Restricted boltzmann machines   NaN     1        1       1  \n",
       "Hopfield network                NaN     1        2       1  \n",
       "Naive bayes classifier            1     4      NaN     NaN  \n",
       "Boltzmann machine               NaN     1        2       2  \n",
       "Autoencoder                       2   NaN        2       1  \n",
       "Artificial neural network         1   NaN      NaN       3  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "    SELECT COLUMN EXPR (WHERE rowCount() > 4)\n",
    "    FROM ml_concepts\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "The results make sense. The words that we found above in the columns are common in Machine Learning concept descriptions. With a plain SQL statement and the `rowCount` function, we reduced our dataset to include words that appear at least 4 times."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Nested JSON example\n",
    "\n",
    "Nested JSON objects can have complex schemas, often involving multi-level and multidimensional data structures. In this section we will create a more complex dataset to illustrate ways to simplify data structures and column selection with [Built-in Function](../../../../doc/#builtin/sql/ValueExpression.md.html#parse_json) and [Column Expression](../../../../doc/#builtin/sql/SelectExpression.md.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's first create an empty dataset called 'toy_example'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "# create dataset\n",
    "print mldb.put('/v1/datasets/toy_example', { \"type\":\"sparse.mutable\" })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will now create one row in the 'toy_example' dataset with the 'row1' JSON object below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<strong>POST http://localhost/v1/datasets/toy_example/commit</strong><br /><strong style=\"color: green;\">200 OK</strong><br /> "
      ],
      "text/plain": [
       "<Response [200]>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "row1 = {\n",
    "  \"name\": \"Bob\", \n",
    "  \"address\": {\"city\": \"Montreal\", \"street\": \"Stanley\"}, \n",
    "  \"sports\": [\"soccer\",\"hockey\"], \n",
    "  \"friends\": [{\"name\": \"Mich\", \"age\": 25}, {\"name\": \"Jean\", \"age\": 28}]\n",
    "}\n",
    "\n",
    "# update dataset by adding a row\n",
    "mldb.post('/v1/datasets/toy_example/rows', {\n",
    "    \"rowName\": \"row1\",\n",
    "    \"columns\": [[\"data\", json.dumps(row1), 0]]\n",
    "})\n",
    "# save changes\n",
    "mldb.post(\"/v1/datasets/toy_example/commit\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will check out our data with a `SELECT` query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>row1</th>\n",
       "      <td>{\"sports\": [\"soccer\", \"hockey\"], \"friends\": [{...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                       data\n",
       "_rowName                                                   \n",
       "row1      {\"sports\": [\"soccer\", \"hockey\"], \"friends\": [{..."
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"SELECT * FROM toy_example\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are many elements within the cell above. We will need to better structure elements within the nested JSON object."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Working with nested JSON objects with built-in functions and column expressions\n",
    "\n",
    "To understand and query nested JSON objects, we will be using a [Built-in Function](../../../../doc/#builtin/sql/ValueExpression.md.html#parse_json) called <code/>parse_json</code> and a [Column Expression](../../../../doc/#builtin/sql/SelectExpression.md.html) <code/>columnPathElement</code>."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is where the [`parse_json`](../../../../doc/#builtin/sql/ValueExpression.md.html#parse_json) function comes in handy. It will help us turn a multidimensional JSON object into a 2D dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>address.city</th>\n",
       "      <th>address.street</th>\n",
       "      <th>friends.0.age</th>\n",
       "      <th>friends.0.name</th>\n",
       "      <th>friends.1.age</th>\n",
       "      <th>friends.1.name</th>\n",
       "      <th>name</th>\n",
       "      <th>sports.0</th>\n",
       "      <th>sports.1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>row1</th>\n",
       "      <td>Montreal</td>\n",
       "      <td>Stanley</td>\n",
       "      <td>25</td>\n",
       "      <td>Mich</td>\n",
       "      <td>28</td>\n",
       "      <td>Jean</td>\n",
       "      <td>Bob</td>\n",
       "      <td>soccer</td>\n",
       "      <td>hockey</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         address.city address.street  friends.0.age friends.0.name  \\\n",
       "_rowName                                                             \n",
       "row1         Montreal        Stanley             25           Mich   \n",
       "\n",
       "          friends.1.age friends.1.name name sports.0 sports.1  \n",
       "_rowName                                                       \n",
       "row1                 28           Jean  Bob   soccer   hockey  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "        SELECT parse_json(data, {arrays: 'parse'}) AS * \n",
    "        FROM toy_example\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`parse_json` is a powerful feature since we can create 2D representations out of multidimensional data. We can read all of the elements of the JSON object on one line. It is also easier to SQL as we will see below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[`columnPathElement`](../../../../doc/#builtin/sql/SelectExpression.md.html) makes it convenient to navigate specific parts of the data structure. In the next block of code, we will do the following:\n",
    "* use [`parse_json`](../../../../doc/#builtin/sql/ValueExpression.md.html#parse_json) to  parse each data element of the object on one row (same as above)\n",
    "* select specific cells using [`columnPathElement`](../../../../doc/#builtin/sql/SelectExpression.md.html) where the the column path name at index = 2 is 'name' (note that 'friends' is at index = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>friends.0.name</th>\n",
       "      <th>friends.1.name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>row1</th>\n",
       "      <td>Mich</td>\n",
       "      <td>Jean</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         friends.0.name friends.1.name\n",
       "_rowName                              \n",
       "row1               Mich           Jean"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "    SELECT COLUMN EXPR (WHERE columnPathElement(2) = 'name') \n",
    "    FROM (\n",
    "        SELECT parse_json(data, {arrays: 'parse'}) AS * NAMED rowPath() FROM toy_example\n",
    "    )\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now know the name of Bob's two friends... As you may have noticed, this is very practical if we want to query a specific attribute of a nested object. The `columnPathElement` [Column Expression](../../../../doc/#builtin/sql/SelectExpression.md.html) allows us to easily query specific JSON data levels or dimensions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Column operations such as the ones shown in this tutorial can be difficult without column expressions. [Column Expressions](../../../../doc/#builtin/sql/SelectExpression.md.html) offer a compact and flexible way to programmatically select columns. It is a great tool to carve out the data that is most needed for your analysis.\n",
    "\n",
    "In this tutorial, we covered three [Column Expressions](../../../../doc/#builtin/sql/SelectExpression.md.html):\n",
    "* `columnName` which returns the name of the columns inside our dataset\n",
    "* `rowCount` which returns the number of non-empty rows for each column\n",
    "* `columnPathElement` which allows us to chose columns at specific sub-levels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Where to next?\n",
    "\n",
    "Check out the other [Tutorials and Demos](../../../../doc/#builtin/Demos.md.html)."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
